﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace NET.Library.Data
{
    /// <summary>
    /// DBSQL
    /// </summary>
    public sealed class DBSQL : DB
    {
        //#region Fields

        //#endregion

        //#region Properties

        //#endregion

        //#region Constructors

        //#endregion

        #region Instance Methods

        #region DataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public SqlDataReader DBExecuteSqlReader(string storedProcedureName)
        {
            return (SqlDataReader)base.DBExecuteScalar(storedProcedureName); //(SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public SqlDataReader DBExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        }
#endregion

        #region DataSet

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public DataSet DBExecuteDataSet(SqlCommand sqlCommand)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                return db.ExecuteDataSet(sqlCommand);
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        ///////// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public DataSet DBExecuteStoredProc(string storedProcName, DataSet dsProcParams)
        //////{
        //////    return base.DBExecuteStoredProc(storedProcName, dsProcParams);
        //////    //SqlCommand sqlCommd = new SqlCommand();

        //////    //try
        //////    //{
        //////    //    sqlCommd.CommandType = CommandType.StoredProcedure;
        //////    //    sqlCommd.CommandText = storedProcName;

        //////    //    if (dsProcParams.Tables.Count > 0)
        //////    //    {
        //////    //        PopulateSQLCommandFromDataTable(sqlCommd, dsProcParams.Tables[0]);
        //////    //    }

        //////    //    return DBExecuteDataSet(sqlCommd);
        //////    //}
        //////    //finally
        //////    //{
        //////    //    sqlCommd.Dispose();
        //////    //}
        //////}

        #endregion

        #region ExecuteNonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQuery(SqlCommand sqlCommand)
        {
            return base.DBExecuteNonQuery(sqlCommand);
        }
        #endregion

        #region ExecuteScalar
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>SqlCommand</returns>
        ///----------------------------------------------------------------------------------
        public SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName)
        {
            return (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues"></param>
        /// <returns>SqlCommand</returns>
        ///----------------------------------------------------------------------------------
        public SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName, parameterValues);
        }
        #endregion

        #region DataReader
        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public  SqlDataReader DBExecuteReader(string storedProcedureName, params object[] parameterValues)
        //////{
        //////    return (SqlDataReader)base.DBExecuteReader(storedProcedureName, parameterValues); 
        //////        //(SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        //////}
        #endregion

        #endregion

        #region Static Methods

        #region DataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the results as a SqlDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>Result as a SqlDataReader.</returns>
        ///----------------------------------------------------------------------------------
        public static SqlDataReader ExecuteSqlReader(string storedProcedureName)
        {
            return (SqlDataReader)DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with parameterValues and returns the results as a SqlDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>Result as a SqlDataReader</returns>
        ///----------------------------------------------------------------------------------
        public static SqlDataReader ExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlDataReader)DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName, parameterValues);
        }
        #endregion

        #region DataSet
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteDataSet(SqlCommand sqlCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DataSet ds = db.ExecuteDataSet(sqlCommand);
                return ds;
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region NonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(SqlCommand sqlCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();
            try
            {
                int rows = db.ExecuteNonQuery(sqlCommand);
                return rows;
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region ExecuteScalar
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns a single result as an object.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>object</returns>
        ///----------------------------------------------------------------------------------
        public static object ExecuteScalar(SqlCommand sqlCommand)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase();
            try
            {
                return db.ExecuteScalar(sqlCommand);
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region Misc
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pSQLCommand"></param>
        /// <param name="pDataTable"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateSQLCommandFromDataTable(SqlCommand pSQLCommand, DataTable pDataTable)
        {
            if (pDataTable.Rows.Count > 0)
            {
                PopulateSQLCommandFromDataRow(pSQLCommand, pDataTable.Columns, pDataTable.Rows[0]);
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pSQLCommand"></param>
        /// <param name="pColCollection"></param>
        /// <param name="pDatarow"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateSQLCommandFromDataRow(SqlCommand pSQLCommand, DataColumnCollection pColCollection, DataRow pDatarow)
        {
            for (int i = 0; i < pColCollection.Count; i++)
            {
                pSQLCommand.Parameters.AddWithValue("@" + pColCollection[i].ColumnName, pDatarow.ItemArray[i]);
            }
        }
        #endregion

        #endregion

    }
}
